/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package binh.data;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import javax.sql.DataSource;
import we.common.CDebug;

/**
 *
 * @author ADMIN
 */
public class MySqlDB implements IDatabase
{

    public static final int MAX_POOL_CONNECTION = 50;
    public static final int MAX_POOL_EXE_THREAD = 10;
    private DataSource dataSource;
    private String DRIVER_NAME = "com.mysql.jdbc.Driver";
    private String URL = "jdbc:mysql://localhost:3306/nhatnhiba";
    private String UNAME = "root";
    private String PWD = "123456";
    private static ExecutorService executor = Executors.newFixedThreadPool(MAX_POOL_EXE_THREAD);

    public MySqlDB()
    {

        dataSource = setupDataSource();
    }

    private Connection getConnection() throws SQLException
    {
        return dataSource.getConnection();
    }

    private DataSource setupDataSource()
    {
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        try
        {
            cpds.setDriverClass(DRIVER_NAME);
        } catch (PropertyVetoException e)
        {
            e.printStackTrace();
        }
        cpds.setJdbcUrl(URL);
        cpds.setUser(UNAME);
        cpds.setPassword(PWD);
        cpds.setMinPoolSize(5);
        cpds.setAcquireIncrement(5);
        cpds.setMaxPoolSize(MAX_POOL_CONNECTION);
        cpds.setMaxStatements(100);
        return cpds;
    }

    @Override
    public CUserData login(String username, String password) throws SQLException
    {

        CUserData Data = new CUserData();        
        ResultSet rs = executeQuery("SELECT * FROM tableuser WHERE UserName = " + "'" + username + "'");
        while (rs.next())
        {
            System.out.println(rs.getString("UserName"));
        }        
        return Data;
    }

    public ResultSet executeQuery(final String query) throws SQLException
    {
        Connection conn = null;
        Statement state = null;

        conn = getConnection();
        state = conn.createStatement();
        ResultSet result = state.executeQuery(query);
        state.close();
        conn.close();
        return result;

    }

    public void asyncExecuteUpdate(final String ex)
    {
        executor.submit(new Runnable()
        {
            @Override
            public void run()
            {
                Connection conn = null;
                Statement state = null;
                try
                {
                    conn = getConnection();
                    state = conn.createStatement();
                    state.execute(ex);
                    state.close();
                    conn.close();
                } catch (SQLException ex)
                {
                    CDebug.Error(this, ex);
                }
            }
        });
    }

    public static void main(String[] args)
    {
        try
        {
            System.out.println("started... ");
            MySqlDB database = new MySqlDB();
            System.out.println("login... ");
            CUserData Data = database.login("DungPro", "sas");
            database.asyncExecuteUpdate("UPDATE tableuser SET realname = 'abc' WHERE Id=130");
            System.out.println("Data + " + Data.Realname);
        } catch (Exception ex)
        {
            ex.printStackTrace();
        }
    }
}
